#!/usr/bin/env python
# -*- coding: utf-8 -*-

#!/usr/bin/env python
from lib.format_time import *
from lib.HandleMysql import *
from lib.handle_mongo import *
from lib.switch_code import *
from lib.load_data import *
from zhineng_conn import *
from create_table import *
from config_tools import *
import urllib
import sys
import re
import time
reload(sys)
sys.setdefaultencoding('utf-8')


def get_order_info( date ,conn ,num):
    stat_conn = set_stats_conn()
    stat_table = "ad_order_info"    
    line_list = []
    
    query = "select id,order_id from orders where mer_id=7202 and date(finish_time)='%s' "%date
    items = conn.get_results(query)
    for item in items:
        
        order_code = item[1]
        if order_code == None:
            continue
        
        order_id = item[0]
        
        cash_amount = 0.0
        discount_amount = 0.0
        refund_cash_amount = 0.0
        refund_discount_amount = 0.0
        #order_code = order_code.replace('topInfo','')
        q = "select order_money,from_account_type,ifnull(refund_money,0) from sub_order where order_id='%s'"%order_id
        its = conn.get_results(q)
        if len(its)>0:
            for it in its:                
                order_amount = it[0]
                amount_type = it[1]
                refund_money = it[2]
                if amount_type == 3:
                    discount_amount += float(order_amount)
                    refund_discount_amount += float(refund_money)
                else:
                    cash_amount += float(order_amount)
                    refund_cash_amount += float(refund_money)
                    
        param = [order_code,order_id,num,cash_amount,discount_amount,refund_cash_amount,refund_discount_amount,date]
        line_list.append('\001'.join(map(lambda i : str(i), param)))
        #print  param;
        
    cont = '\n'.join(line_list)
    path = "/work3/opt/analytic/lh_tmp_data/zhineng/order/"
    file_name = "zn_order_info_%s_%s.txt"%(date.replace('-',""),num)
    writeTextToFile(path,file_name,"w",cont)
    
    load_data(stat_conn,path+file_name,stat_table,'\001')    
    #remove_file(path+file_name)
    
    conn.close()
    stat_conn.close()


def load_click_info(date):
    stat_conn = set_stats_conn()    
    date_str = date.replace('-',"")
    line_list = [] 
    #pattern =  re.compile(r'(\w*)')
    month = get_date_info(date,'%Y%m')
    stat_table = "click_consumption_%s" %month
    tomorrow = var_date(date).replace('-',"")    
   
    path = "/work3/opt/analytic/lh_tmp_data/zhineng/click_data/"
    file_name = "click_data_%s.txt"%date_str
    #writeTextToFile(path,file_name,"w",cont)

    ## create table
    create_click_table(date)
    
    q = "delete from %s where stat_date='%s'"%(stat_table,date)
    stat_conn.execute_sql(q)

    load_data(stat_conn,path+file_name,stat_table,'$$')
    #remove_file(path+file_name)
    
    stat_conn.close()

def create_click_table(date):
	print 'create click table'

# refund info
def get_refund_info(date):
    dict_conn = set_dict_conn()
    stat_conn = set_stats_conn()    
    date_str = date.replace('-',"")
    line_list = [] 
    #pattern =  re.compile(r'(\w*)')
    stat_table = "ad_refund_info"
    tomorrow = var_date(date).replace('-',"")    
    
    for i in range(24):
        j = '%s'%i
        if i < 10:
            j = "0%s"%i
        url_str = "http://10.5.12.160:50075/streamFile/dsap/rawdata/znlog/%s/refund_%s%s"%(date_str,date_str,j)
        if i > 21:
            url_str = "http://10.5.12.160:50075/streamFile/dsap/rawdata/znlog/%s/refund_%s%s"%(tomorrow,date_str,j)
        #print url_str
        file_path = "/work3/opt/analytic/lh_tmp_data/zhineng/refund_info_%s_%s.txt"%(date_str,i)
        
        #print url_str,file_path    
        urllib.urlretrieve(url_str, file_path)                       
        
        f = open(file_path)
        for line in f.readlines():
            arr = line.split('\t')
            #print arr
            #print len(arr)
            if len(arr)<27:
                remove_file(file_path)
                continue
            
            refund_id = arr[0]
            refund_time_id = arr[1]
            refund_time = format_timestamp(refund_time_id)
            user_id = arr[2]
            plan_id = arr[3]
            unit_id = arr[4]
            entity_id = arr[5]
            entity_type = arr[6]
            entity_cate_id = arr[7]
            entity_city_id = arr[8]
            
            display_id = arr[9]
            city = arr[11]
            if city == '0':
                remove_file(file_path)
                continue
            cate = arr[12]
            
            display_position = arr[13]
            sort_position = arr[14]
            amount_str = arr[15]
            amount = 0.0
            if amount_str != None and amount_str!='':
                amount = float(amount_str)/100
            budget_amount = arr[26]
            budget_amount=budget_amount.replace('\n','')
                        
            cash_amount = 0
            discount_amount = 0
            q = "select refund_cash_amount,refund_discount_amount from ad_order_info where click_id='%s'"%refund_id
            items = stat_conn.get_results(q)
            if len(items) > 0 :
                cash_amount = items[0][0]
                discount_amount = items[0][1]
            
            dc = get_cates(cate,dict_conn)    
            cat1 = 0
            cat1name = ''
            cat2 = 0
            cat2name=''
            cat3 = 0
            cat3name=''
            if dc != None:
                if len(dc) > 0:
                    cat1 = dc[0][0]
                    cat1name = dc[0][1]
                if len(dc) > 1:
                    cat2 = dc[1][0]
                    cat2name=dc[1][1]
                if len(dc) > 2:
                    cat3 = dc[2][0]
                    cat3name=dc[2][1]
                    
            city_id = 0
            city_name = ''
            area_id = 0
            area_name = ''
            local_id= 0
            local_name = ''
            dc = get_citys(city,dict_conn)
            if dc != None:
                if len(dc) >0:
                    city_id = dc[0][0]
                    city_name = dc[0][1]
                if len(dc)>1:
                    area_id = dc[1][0]
                    area_name = dc[1][1]
                if len(dc)>2:
                    local_id= dc[2][0]
                    local_name = dc[2][1]
            
            param = [date,refund_id,refund_time,user_id,plan_id,unit_id,entity_id,entity_type,entity_cate_id,entity_city_id,city_id,city_name,area_id,area_name,local_id,local_name,cat1,cat1name,cat2,cat2name,cat3,cat3name,display_id,display_position,sort_position,amount,cash_amount,discount_amount,budget_amount]
            line_list.append('\001'.join(map(lambda i : str(i), param)))
        remove_file(file_path)
            
    cont = '\n'.join(line_list)
    #cont = cont + '\n'
    path = "/work3/opt/analytic/lh_tmp_data/zhineng/refund_data/"
    file_name = "refund_data_%s.txt"%date_str
    writeTextToFile(path,file_name,"w",cont)
    
    load_data(stat_conn,path+file_name,stat_table,'\001')
    #remove_file(path+file_name)
    
    stat_conn.close()
    dict_conn.close()
    

# display info
def get_display_info(date):
    dict_conn = set_dict_conn()
    ib_conn = set_ib_conn()
    city_list = get_city_list(dict_conn)
    cate_list = {}
        
    date_str = date.replace('-',"")
    line_list = [] 
    month = get_date_info(date,'%Y%m')
    stat_table = "ad_display_info%s"%month
    print "get_display_info funcation. download ad_display_info file."
    try:
        #pass
        create_display_info(ib_conn,stat_table)
    except Exception,e:
        print 'get_display_info ib create ad_display_info table error:',e
    ib_conn.close()
    #return
    
    #sql =
    print "downloading display info file."
    for i in range(4):
        #/dsap/resultdata/ec_data/DispLog_Job/20130518/part-r-00000
        url_str = "http://10.5.12.160:50075/streamFile/dsap/resultdata/ec_data/DispLog_Job/full/%s/part-r-0000%s"%(date_str,i)
        
        #url_str = "http://10.5.12.160:50075/streamFile/dsap/middata/ec_data/DispLog_Job/tmp4/%s/part-r-0000%s"%(date_str,i)
        #print url_str
        file_path = "/work3/opt/analytic/lh_tmp_data/zhineng/display_info_%s_%s.txt"%(date_str,i)
        
        #print url_str,file_path    
        urllib.urlretrieve(url_str, file_path)                       
        
        f = open(file_path)
        for line in f.readlines():
            #finalbottom_10015_103_13418826665734_178673_862483_0_5:1
            #print line
            arr = line.split(':')
            #print arr
            #print len(arr)
            if len(arr)<2:
                continue
            
            key_str = arr[0]
            display_count = arr[1]
            display_count = display_count.replace('\n','')
            
            
            item = key_str.split('_')            
            if len(item)<8:
                continue
            position = item[0]
            city = '0'
            try:
                city = item[1]
            except Exception:
                continue
            
            if len(city) > 6:
                continue
            
            if city == '0':
                continue
            
            cate = item[2]
            user_id = item[3]
            plan_id = item[4]
            unit_id = item[5]
            entity_id = item[6]
            entity_type = item[7]
            
            if len(cate) > 6:
                continue
            
            dc = None
            if cate_list.has_key(cate):
                dc = cate_list[cate]
            else:
                try:
                    dc = get_cates(cate,dict_conn)
                    cate_list[cate] = dc
                except Exception,e:
                    print "cate %s get cate info error." %cate,e
                    continue
            cat1 = 0
            cat1name = ''
            cat2 = 0
            cat2name=''
            cat3 = 0
            cat3name=''
            if dc != None:
                if len(dc) > 0:
                    cat1 = dc[0][0]
                    cat1name = dc[0][1]
                if len(dc) > 1:
                    cat2 = dc[1][0]
                    cat2name=dc[1][1]
                if len(dc) > 2:
                    cat3 = dc[2][0]
                    cat3name=dc[2][1]
                    
            city_id = 0
            city_name = ''
            area_id = 0
            area_name = ''
            local_id= 0
            local_name = ''
            
            if city_list.has_key(city):
                city_id = city
                city_name = city_list[city]
            else:
                try:
                    dc = get_citys(city,dict_conn)
                except Exception,e:
                    print "city %s get citys error." %city,e
                    continue
                if dc != None:
                    if len(dc) >0:
                        city_id = dc[0][0]
                        city_name = dc[0][1]
                    if len(dc)>1:
                        area_id = dc[1][0]
                        area_name = dc[1][1]
                    if len(dc)>2:
                        local_id= dc[2][0]
                        local_name = dc[2][1]
                    
            param = [date,city_id,city_name,area_id,area_name,local_id,local_name,cat1,cat1name,cat2,cat2name,cat3,cat3name,position,user_id,plan_id,unit_id,entity_id,entity_type,display_count]
            line_list.append('\001'.join(map(lambda i : str(i), param)))
        remove_file(file_path)
            
        cont = '\n'.join(line_list)
        cont = cont + '\n'
        path = "/work3/opt/analytic/lh_tmp_data/zhineng/display_data/"
        file_name = "display_data_%s_%s.txt"%(date_str,i)
        #print "write display info to file . path:%s,file name: %s" %(path,file_name)
        writeTextToFile(path,file_name,"w",cont)
        
        try:
           ib_conn  = set_ib_conn()
           load_data(ib_conn,path+file_name,stat_table,'\001')
        except Exception,e:
           print "ib ad_display_info load error:",e
        line_list = [] 
        #remove_file(path+file_name)
        ib_conn.close()
    dict_conn.close()

# display info
def get_display_info_for_day(date):
    stat_conn = set_stats_conn()
    ib_conn = set_ib_conn()
    
    date_str = date.replace('-',"")
    month = get_date_info(date,'%Y%m%d')
    stat_table = "ad_display_info%s"%month
    try:
        #pass
        create_display_info(ib_conn,stat_table)
    except Exception,e:
        print 'ib create display info table error:',e
    try:
	create_display_info(stat_conn,stat_table)
    except Exception,e:
	print 'stat db create display info table error:',e
        
    path = "/work3/opt/analytic/lh_tmp_data/zhineng/display_data/"
    
    for i in range(4):
        print "begin load display data."
        file_name = "display_data_%s_%s.txt"%(date_str,i)
        load_data(ib_conn,path+file_name,stat_table,'\001')
	load_data(stat_conn,path+file_name,stat_table,'\001')

    
    ib_conn = set_ib_conn()
    stat_conn.close()

def get_click_income_stats(date):
    print 'start get_click_income_stats'
    ad_conn = set_58_conn()
    stat_conn = set_stats_conn()
    dict_conn = set_dict_conn()
    ib_conn = set_ib_conn()
    city_dict = {}
    cate_dict = {}
    
    vip_user_dict = {}
    query = "select user_id from order_info where product_type=10 and pay_amount>0 and left(service_begin_time,10)<='%s' and left(service_end_time,10)>='%s'; "%(date,date)
    items = ad_conn.get_results(query)
    for item in items:
        user_id = item[0]
        vip_user_dict[str(user_id)] = 1
        
    date_str = date.replace('-',"")
    line_list = [] 
    #pattern =  re.compile(r'(\w*)')
    month = get_date_info(date,'%Y%m')
    stat_table = "ad_click_income_stats_%s"%month
    
    query = "select user_id,plan_id,unit_id,entity_id,entity_type,city_id,city_name,area_id,area_name,local_id,local_name,cate1_id,cate1_name,cate2_id,cate2_name,cate3_id,cate3_name,display_position,sum(case when click_state=1 then amount end),sum(cash_amount),sum(discount_amount),bid,count(1),sum(budget_amount),entity_cate_id,entity_city_id,count(case when click_state=1 then 1 end) from click_consumption where stat_date='%s' group by user_id,plan_id,unit_id,entity_id,entity_type,city_id,area_id,local_id,cate1_id,cate2_id,cate3_id,display_position"%date
    items = stat_conn.get_results(query)
    for item in items:
        user_id = item[0]
        plan_id = item[1]
        unit_id = item[2]
        entity_id = item[3]
        entity_type = item[4]
        city_id = item[5]
        city_name = item[6]
        area_id = item[7]
        area_name = item[8]
        local_id = item[9]
        local_name = item[10]
        cate1_id = item[11]
        cate1_name = item[12]
        cate2_id = item[13]
        cate2_name = item[14]
        cate3_id = item[15]
        cate3_name = item[16]
        position = item[17]
        amount = item[18]
        cash_amount = item[19]
        discount_amount = item[20]
        bid = item[21]
        click_count = item[22]
        budget_amount = item[23]
        
        entity_cate = item[24]
        entity_city = item[25]
        eff_click_count = item[26]
        #cate & city
        dc = None
        if cate_dict.has_key(str(entity_cate)):
            dc = cate_dict[str(entity_cate)]
        else:
            dc = get_gs_cates(entity_cate,dict_conn)
            cate_dict[str(entity_cate)] = dc
            
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name=''
        cat3 = 0
        cat3name=''
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name=dc[1][1]
            if len(dc) > 2:
                cat3 = dc[2][0]
                cat3name=dc[2][1]
                
        entity_city_id = 0
        entity_city_name = ''
        entity_area_id = 0
        entity_area_name = ''
        entity_local_id= 0
        entity_local_name = ''
        
        dc = None
        if city_dict.has_key(str(entity_city)):
            dc = city_dict[str(entity_city)]
        else:
            dc = get_citys(entity_city,dict_conn)
            city_dict[str(entity_city)] = dc
            
        if dc != None:
            if len(dc) >0:
                entity_city_id = dc[0][0]
                entity_city_name = dc[0][1]
            if len(dc)>1:
                entity_area_id = dc[1][0]
                entity_area_name = dc[1][1]
            if len(dc)>2:
                entity_local_id= dc[2][0]
                entity_local_name = dc[2][1]
        #creative id
        creative_id = 0
        q = "select creative_id  from creative_info where subscribe_id=%s"%unit_id
        its = stat_conn.get_results(q)
        if len(its) > 0:
            creative_id = its[0][0]
        
        #refund info
        refund_amount = 0.0
        refund_cash_amount = 0.0
        refund_discount_amount = 0.0
        refund_count = 0
        #q = "select ifnull(sum(amount),0),ifnull(sum(cash_amount),0),ifnull(sum(discount_amount),0),count(1) from ad_refund_info where stat_date='%s' and unit_id=%s and cate2_id=%s and city_id=%s and display_position='%s'"%(date,unit_id,cate2_id,city_id,position)
        #its = stat_conn.get_results(q)
        #if len(its) > 0:
        #    refund_amount = its[0][0]
        #    refund_cash_amount = its[0][1]
        #    refund_discount_amount = its[0][2]
        #    refund_count = its[0][2]
        #click_count = click_count - refund_count
        
        #is vip
        is_vip = 0
        if vip_user_dict.has_key(str(user_id)):
            is_vip = vip_user_dict[str(user_id)]
            
        #display_count
        display_count = 0
        tb_name = "ad_display_info%s"%get_date_info(date,'%Y%m%d')
        q = "select ifnull(sum(display_count),0) from %s where stat_date='%s' and unit_id=%s and cate2_id=%s and city_id=%s and position='%s'"%(tb_name,date,unit_id,cate2_id,city_id,position)
        its = ib_conn.get_results(q)
        display_count = its[0][0]
        
        param = [date,user_id,is_vip,creative_id,plan_id,unit_id,entity_id,entity_type,city_id,city_name,area_id,area_name,local_id,local_name,cate1_id,cate1_name,cate2_id,cate2_name,cate3_id,cate3_name,position,display_count,click_count,bid,budget_amount,amount,cash_amount,discount_amount,refund_amount,refund_cash_amount,refund_discount_amount,entity_city_id,entity_city_name,entity_area_id,entity_area_name,entity_local_id,entity_local_name,cat1,cat1name,cat2,cat2name,cat3,cat3name,eff_click_count]
        line_list.append('\001'.join(map(lambda i : str(i), param)))
    
    cont = '\n'.join(line_list)
    #cont = cont + '\n'
    path = "/work3/opt/analytic/lh_tmp_data/zhineng/income/"
    file_name = "click_income%s.txt" % date_str
    writeTextToFile(path,file_name,"w",cont)
    
    q="delete from %s where stat_date='%s'"% (stat_table, date)
    stat_conn.execute_sql(q)
    
    load_data(stat_conn,path+file_name,stat_table,'\001')
    #remove_file(path+file_name)
    
    ib_conn.close()
    stat_conn.close()
    ad_conn.close()
    
def format_timestamp(ts_s):
    try:
        ts = int(ts_s)/1000
        return time.strftime('%Y-%m-%d %H:%M:%S', time.gmtime(ts))
    except Exception:
        return get_yesterday()
   
def get_all_order_info(date):
    print "order"
    try:
        pass
        order_conn = set_order0_conn()
        get_order_info(date,order_conn,0)
        order_conn = set_order1_conn()
        get_order_info(date,order_conn,1)
        order_conn = set_order2_conn()
        get_order_info(date,order_conn,2)
        order_conn = set_order3_conn()
        get_order_info(date,order_conn,3)
    except Exception,e:
        print 'get_order_info:',e
        
    try:
        pass
        get_click_info(date)
    except Exception,e:
        print 'get_click_info:',e
        
def get_disp(date):
    try:
        pass
        print "begin get disp funcation. part get display info"
        get_display_info(date)
    except Exception,e:
        print 'getting_display_info:',e
    try:
        pass
        print "begin get disp funcation. part get display info for day"
        get_display_info_for_day(date)
    except Exception,ex:
        print "get display info for day error:",ex

def execute(date):
    print "income"
    #time.sleep(300)
    try:
        pass
        #get_refund_info(date)
    except Exception,e:
        print 'get_refund_info:',e
    
    #time.sleep(600)
    try:
        pass
        get_click_income_stats(date)
    except Exception,e:
        print 'get_click_income_stats:',e
    
def main():
   inp = 0
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        inp = sys.argv[1]
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])

   print "begin extract zhineng order info from %s to %s" %(start_date,end_date)    
 
   if inp == "1":
        yesterday = get_yesterday()
        get_all_order_info(yesterday)
   elif inp == "2":
        yesterday = get_yesterday()
        print yesterday
        get_disp(yesterday)
   else:
        stat_date=start_date   
        #conn = set_stats_conn()
        while stat_date>=start_date and stat_date<=end_date:
             print "The program of the date of %s is beginning at %s " %(stat_date,get_now())
             #execute( stat_date)
             print "getting click income stats in %s" %stat_date
             load_click_info(stat_date)
             
             print "The program of the date of %s is end at %s " %(stat_date,get_now())
             stat_date=var_date(stat_date)


if __name__=='__main__':
    main()

